using System;
using System.Data;
using System.Data.OleDb;
using System.Runtime.InteropServices;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;
using System.Text;

namespace PCSComProcurement.Purchase.DS
{
    public class PO_DeliveryScheduleDS
    {
        public PO_DeliveryScheduleDS()
        {
        }

        private const string THIS = "PCSComProcurement.Purchase.DS.DS.PO_DeliveryScheduleDS";
        public void Add(object pobjObjectVO)
        {
            const string METHOD_NAME = THIS + ".Add()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                PO_DeliveryScheduleVO objObject = (PO_DeliveryScheduleVO)pobjObjectVO;
                string strSql = String.Empty;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand("", oconPCS);

                strSql = "INSERT INTO PO_DeliverySchedule("
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD + ")"
                    + "VALUES(?,?,?,?,?,?)";

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.DELIVERYLINE_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.DELIVERYLINE_FLD].Value = objObject.DeliveryLine;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.SCHEDULEDATE_FLD, OleDbType.Date));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.SCHEDULEDATE_FLD].Value = objObject.ScheduleDate;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD].Value = objObject.DeliveryQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD].Value = objObject.ReceivedQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.ADJUSTMENT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.ADJUSTMENT_FLD].Value = objObject.Adjustment;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD].Value = objObject.PurchaseOrderDetailID;


                ocmdPCS.CommandText = strSql;
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();

            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void Delete(int pintID)
        {
            const string METHOD_NAME = THIS + ".Delete()";
            string strSql = String.Empty;
            strSql = "DELETE " + PO_DeliveryScheduleTable.TABLE_NAME + " WHERE  " + "DeliveryScheduleID" + "=" + pintID.ToString();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
                ocmdPCS = null;

            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }


            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void DeleteDeliveryDetail(int pintPurchaseOrderLineID)
        {
            const string METHOD_NAME = THIS + ".DeleteDeliveryDetail()";
            string strSql = String.Empty;
            strSql = "DELETE " + PO_DeliveryScheduleTable.TABLE_NAME
                + " WHERE  " + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD + "=" + pintPurchaseOrderLineID.ToString();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
                ocmdPCS = null;

            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }


            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public object GetObjectVO(int pintID)
        {
            const string METHOD_NAME = THIS + ".GetObjectVO()";
            DataSet dstPCS = new DataSet();

            OleDbDataReader odrPCS = null;
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                strSql = "SELECT "
                    + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD
                    + " FROM " + PO_DeliveryScheduleTable.TABLE_NAME
                    + " WHERE " + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + "=" + pintID;

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                odrPCS = ocmdPCS.ExecuteReader();

                PO_DeliveryScheduleVO objObject = new PO_DeliveryScheduleVO();

                while (odrPCS.Read())
                {
                    objObject.DeliveryScheduleID = int.Parse(odrPCS[PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD].ToString());
                    objObject.DeliveryLine = int.Parse(odrPCS[PO_DeliveryScheduleTable.DELIVERYLINE_FLD].ToString());
                    objObject.ScheduleDate = DateTime.Parse(odrPCS[PO_DeliveryScheduleTable.SCHEDULEDATE_FLD].ToString());
                    objObject.DeliveryQuantity = Decimal.Parse(odrPCS[PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD].ToString());
                    try
                    {
                        objObject.ReceivedQuantity = Decimal.Parse(odrPCS[PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD].ToString());
                    }
                    catch
                    {
                        objObject.ReceivedQuantity = 0;
                    }
                    try
                    {
                        objObject.Adjustment = Decimal.Parse(odrPCS[PO_DeliveryScheduleTable.ADJUSTMENT_FLD].ToString());
                    }
                    catch
                    {
                        objObject.Adjustment = 0;
                    }
                    objObject.PurchaseOrderDetailID = int.Parse(odrPCS[PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD].ToString());

                }
                return objObject;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }

            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void Update(object pobjObjecVO)
        {
            const string METHOD_NAME = THIS + ".Update()";

            PO_DeliveryScheduleVO objObject = (PO_DeliveryScheduleVO)pobjObjecVO;


            //prepare value for parameters
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                strSql = "UPDATE PO_DeliverySchedule SET "
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + "=   ?" + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + "=   ?" + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + "=   ?" + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + "=   ?" + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + "=   ?" + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD + "=  ?"
                    + " WHERE " + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + "= ?";

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.DELIVERYLINE_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.DELIVERYLINE_FLD].Value = objObject.DeliveryLine;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.SCHEDULEDATE_FLD, OleDbType.Date));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.SCHEDULEDATE_FLD].Value = objObject.ScheduleDate;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD].Value = objObject.DeliveryQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD].Value = objObject.ReceivedQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.ADJUSTMENT_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.ADJUSTMENT_FLD].Value = objObject.Adjustment;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD].Value = objObject.PurchaseOrderDetailID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD].Value = objObject.DeliveryScheduleID;


                ocmdPCS.CommandText = strSql;
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }

        public DataSet List()
        {
            const string METHOD_NAME = THIS + ".List()";
            DataSet dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                    + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD
                    + " FROM " + PO_DeliveryScheduleTable.TABLE_NAME;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PO_DeliveryScheduleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }

        public DataSet List(int pintPOMasterID)
        {
            const string METHOD_NAME = THIS + ".List()";
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = "SELECT "
                    + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD
                    + " FROM " + PO_DeliveryScheduleTable.TABLE_NAME
                    + " Where " + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD + " IN ("
                    + " SELECT " + PO_PurchaseOrderDetailTable.PURCHASEORDERDETAILID_FLD
                    + " FROM " + PO_PurchaseOrderDetailTable.TABLE_NAME
                    + " WHERE " + PO_PurchaseOrderDetailTable.PURCHASEORDERMASTERID_FLD + " = " + pintPOMasterID + ")";
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PO_DeliveryScheduleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }

        public DataSet List(string pstrScheduleIDs)
        {
            const string METHOD_NAME = THIS + ".List()";
            DataSet dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = "SELECT "
                    + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ","
                    + PO_DeliveryScheduleTable.STARTDATE_FLD + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD
                    + " FROM " + PO_DeliveryScheduleTable.TABLE_NAME
                    + " WHERE " + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + " IN (" + pstrScheduleIDs + ")";
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PO_DeliveryScheduleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet ListByReceipt(int pintReceiptID)
        {
            const string METHOD_NAME = THIS + ".ListByReceipt()";
            DataSet dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = "SELECT DISTINCT "
                    + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ","
                    + PO_DeliveryScheduleTable.STARTDATE_FLD + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD
                    + " FROM " + PO_DeliveryScheduleTable.TABLE_NAME
                    + " WHERE " + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + " IN (SELECT "
                    + PO_PurchaseOrderReceiptDetailTable.DELIVERYSCHEDULEID_FLD
                    + " FROM " + PO_PurchaseOrderReceiptDetailTable.TABLE_NAME
                    + " WHERE " + PO_PurchaseOrderReceiptDetailTable.PURCHASEORDERRECEIPTID_FLD + "=" + pintReceiptID
                    + ")";
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PO_DeliveryScheduleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet GetDeliverySchedule(int pintPurchaseOrderLineID)
        {
            const string METHOD_NAME = THIS + ".GetDeliverySchedule()";
            DataSet dstPCS = new DataSet();

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                    + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ","
                    + "0 Warning,"
                    + PO_DeliveryScheduleTable.STARTDATE_FLD + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD
                    + " FROM " + PO_DeliveryScheduleTable.TABLE_NAME
                    + " WHERE " + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD + "=" + pintPurchaseOrderLineID;

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PO_DeliveryScheduleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }

        public void UpdateDataSet(DataSet pData)
        {
            const string METHOD_NAME = THIS + ".UpdateDataSet()";
            string strSql;
            OleDbConnection oconPCS = null;
            OleDbCommandBuilder odcbPCS;
            OleDbDataAdapter odadPCS = new OleDbDataAdapter();

            try
            {
                strSql = "SELECT "
                    + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD
                    + " FROM " + PO_DeliveryScheduleTable.TABLE_NAME;

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
                odcbPCS = new OleDbCommandBuilder(odadPCS);
                pData.EnforceConstraints = false;
                odadPCS.Update(pData, PO_DeliveryScheduleTable.TABLE_NAME);

            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }

                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }

        public void UpdateDeliveryDataSet(DataSet pData, int pintPurchaseOrderDetailID)
        {
            const string METHOD_NAME = THIS + ".UpdateDataSet()";
            string strSql;
            OleDbConnection oconPCS = null;
            OleDbCommandBuilder odcbPCS;
            OleDbDataAdapter odadPCS = new OleDbDataAdapter();

            try
            {
                UpdateDataSet(pData);
                strSql = "SELECT "
                    + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD
                    + " FROM " + PO_DeliveryScheduleTable.TABLE_NAME;
                strSql += " WHERE " + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD + "=" + pintPurchaseOrderDetailID;
                pData.Clear();
                odadPCS.SelectCommand.CommandText = strSql;
                odadPCS.SelectCommand.CommandType = CommandType.Text;
                odadPCS.Fill(pData, pData.Tables[0].TableName);
                pData.AcceptChanges();
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }

                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }

        public int GetMaxDeliveryScheduleLine(int pintPurchaseOrderDetailID)
        {
            const string METHOD_NAME = THIS + ".GetMaxDeliveryScheduleLine()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                strSql = "SELECT max ("
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ")"
                    + " FROM " + PO_DeliveryScheduleTable.TABLE_NAME
                    + " WHERE " + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD + "=" + pintPurchaseOrderDetailID;

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                string strResult = ocmdPCS.ExecuteScalar().ToString();
                int intResult;
                try
                {
                    intResult = int.Parse(strResult);
                }
                catch
                {
                    intResult = 0;
                }
                return intResult;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public decimal GetTotalDeliveryQuantityOfLine(int pintPODetailID)
        {
            const string METHOD_NAME = THIS + ".GetTotalDeliveryQuantityOfLine()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                strSql = "SELECT ISNULL(SUM(ISNULL(DeliveryQuantity,0)),0) AS DeliveryQuantity"
                    + " FROM PO_DeliverySchedule WHERE PurchaseOrderDetailID = " + pintPODetailID;

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                object objResult = ocmdPCS.ExecuteScalar();
                try
                {
                    return Convert.ToDecimal(objResult);
                }
                catch
                {
                    return decimal.Zero;
                }
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void UpdateSubstractQuantityReceipt(int printDeliveryScheduleId, decimal decQuantityReceipt)
        {
            const string METHOD_NAME = THIS + ".UpdateSubstractQuantityReceipt()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                strSql = "UPDATE PO_DeliverySchedule SET "
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + "=" + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + " -   ?"
                    + " WHERE " + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + "= ?";

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD].Value = decQuantityReceipt;

                ocmdPCS.Parameters.Add(new OleDbParameter(PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD].Value = printDeliveryScheduleId;

                ocmdPCS.CommandText = strSql;
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
        public void UpdateCancelDelivery(DataSet pData)
        {
            const string METHOD_NAME = THIS + ".UpdateDataSet()";
            string strSql;
            OleDbConnection oconPCS = null;
            OleDbCommandBuilder odcbPCS;
            OleDbDataAdapter odadPCS = new OleDbDataAdapter();

            try
            {
                strSql = "SELECT "
                    + PO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYLINE_FLD + ","
                    + PO_DeliveryScheduleTable.SCHEDULEDATE_FLD + ","
                    + PO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.RECEIVEDQUANTITY_FLD + ","
                    + PO_DeliveryScheduleTable.ADJUSTMENT_FLD + ","
                    + PO_DeliveryScheduleTable.CANCELDELIVERY_FLD + ","
                    + PO_DeliveryScheduleTable.PURCHASEORDERDETAILID_FLD
                    + " FROM " + PO_DeliveryScheduleTable.TABLE_NAME;

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
                odcbPCS = new OleDbCommandBuilder(odadPCS);
                pData.EnforceConstraints = false;
                odadPCS.Update(pData, PO_DeliveryScheduleTable.TABLE_NAME);
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }

                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }
        public DataSet SearchDeliverySchedule(DateTime pdtmFromDate, DateTime pdtmToDate, int pintPOMasterID,
            int pintCategoryID, string pstrProductID)
        {
            const string METHOD_NAME = THIS + ".SearchDeliverySchedule()";
            DataSet dstPCS = new DataSet();

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                StringBuilder strFilter = new StringBuilder();
                strSql.Append("SELECT M.Code PONo, D.Line, S.DeliveryLine, C.Code ITM_CategoryCode, P.Code, P.Description, Revision, U.Code BuyingUM, ");
                strSql.Append(" S.ScheduleDate, D.OrderQuantity, S.DeliveryQuantity, ");
                strSql.Append(" ISNULL(CancelDelivery,0) CancelDelivery,");
                strSql.Append(" P.CategoryID, D.PurchaseOrderMasterID, D.PurchaseOrderDetailID, S.DeliveryScheduleID, P.ProductID,");
                strSql.Append(" ReceivedQuantity, Adjustment, StartDate");
                strSql.Append(" FROM PO_PurchaseOrderMaster M JOIN PO_PurchaseOrderDetail D");
                strSql.Append(" ON M.PurchaseOrderMasterID = D.PurchaseOrderMasterID");
                strSql.Append(" JOIN PO_DeliverySchedule S ON D.PurchaseOrderDetailID = S.PurchaseOrderDetailID");
                strSql.Append(" JOIN ITM_Product P ON D.ProductID = P.ProductID");
                strSql.Append(" LEFT JOIN ITM_Category C ON P.CategoryID = C.CategoryID");
                strSql.Append(" LEFT JOIN MST_UnitOfMeasure U ON D.BuyingUMID = U.UnitOfMeasureID");

                if (pdtmFromDate != DateTime.MinValue)
                {
                    if (strFilter.Length > 0)
                        strFilter.Append(" AND ");
                    strFilter.Append(" ScheduleDate >= ?");
                    ocmdPCS.Parameters.Add(new OleDbParameter("FromDate", OleDbType.Date)).Value = pdtmFromDate;
                }
                if (pdtmToDate != DateTime.MinValue)
                {
                    if (strFilter.Length > 0)
                        strFilter.Append(" AND ");
                    strFilter.Append(" ScheduleDate <= ?");
                    ocmdPCS.Parameters.Add(new OleDbParameter("ToDate", OleDbType.Date)).Value = pdtmToDate;
                }
                if (pintPOMasterID > 0)
                    strFilter.Append( " AND D.PurchaseOrderMasterID = " + pintPOMasterID);
                if (pintCategoryID > 0)
                {
                    if (strFilter.Length > 0)
                        strFilter.Append(" AND ");
                    strFilter.Append(" AND P.CategoryID = " + pintCategoryID);
                }
                if (pstrProductID != null && pstrProductID != string.Empty)
                {
                    if (strFilter.Length > 0)
                        strFilter.Append(" AND ");
                    strFilter.Append(" AND P.ProductID IN (" + pstrProductID + ")");
                }
                if (strFilter.Length > 0) strFilter.Insert(0, " WHERE ");
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);               
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PO_DeliveryScheduleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void UpdateDelivery(string pstrCancelList, string pstrApprovedList)
        {
            const string METHOD_NAME = THIS + ".UpdateDelivery()";
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = string.Empty;
                if (pstrCancelList != string.Empty)
                    strSql += "UPDATE PO_DeliverySchedule SET CancelDelivery = 1"
                        + " WHERE DeliveryScheduleID IN (" + pstrCancelList.Substring(0, pstrCancelList.Length - 1) + ");";
                if (pstrApprovedList != string.Empty)
                    strSql += "UPDATE PO_DeliverySchedule SET CancelDelivery = 0"
                        + " WHERE DeliveryScheduleID IN (" + pstrApprovedList.Substring(0, pstrApprovedList.Length - 1) + ");";
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
                ocmdPCS = null;
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                else
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
    }
}